import subprocess

# Hive命令列表
hive_commands = [
    "hive -e \"load data local inpath '/opt/data_users.csv' into table data_shixun.ods_user;\"",
    "hive -e \"load data local inpath '/opt/data_opus.csv' into table data_shixun.ods_opu;\"",
    "hive -e \"use shixun_data;\"",
    """
    hive -e "
    INSERT INTO TABLE ads_age (age_range, count)
    SELECT 
        CASE 
            WHEN age BETWEEN 10 AND 19 THEN '10-19'
            WHEN age BETWEEN 20 AND 29 THEN '20-29'
            WHEN age BETWEEN 30 AND 39 THEN '30-39'
            WHEN age BETWEEN 40 AND 49 THEN '40-49'
            WHEN age BETWEEN 50 AND 59 THEN '50-59'
            ELSE '60及以上'
        END AS age_range,
        COUNT(*) AS count
    FROM 
        ods_user
    GROUP BY 
        CASE 
            WHEN age BETWEEN 10 AND 19 THEN '10-19'
            WHEN age BETWEEN 20 AND 29 THEN '20-29'
            WHEN age BETWEEN 30 AND 39 THEN '30-39'
            WHEN age BETWEEN 40 AND 49 THEN '40-49'
            WHEN age BETWEEN 50 AND 59 THEN '50-59'
            ELSE '60及以上'
        END
    ORDER BY 
        MIN(age);"
    """,
    """
    hive -e "
    INSERT INTO TABLE ads_province (province_name, province_number)
    SELECT 
        t2.province_name AS province_name,
        t1.province_number AS province_number
    FROM
        (SELECT 
             province_id, 
             COUNT(*) AS province_number 
         FROM 
             ods_user 
         GROUP BY 
             province_id) t1
    JOIN
        dim_province t2
    ON 
        t1.province_id = t2.province_id;"
    """,
    """
    hive -e "
    INSERT INTO TABLE ads_sex (sex, sex_number)
    SELECT 
        sex,
        COUNT(*) AS sex_number
    FROM 
        ods_user
    GROUP BY 
        sex;"
    """,
    """
    hive -e "
    INSERT INTO TABLE ads_times (times_name, times_number)
    SELECT 
        t2.times_name AS times_name,
        t1.times_number AS times_number
    FROM
        (SELECT 
             times_id, 
             COUNT(*) AS times_number 
         FROM 
             ods_user 
         GROUP BY 
             times_id) t1
    JOIN
        dim_times t2
    ON 
        t1.times_id = t2.times_id;"
    """,
    """
    hive -e "
    INSERT INTO TABLE ads_market (market_name, market_number)
    SELECT 
        t2.market_name AS market_name,
        t1.market_number AS market_number 
    FROM
        (SELECT 
             market_id, 
             COUNT(*) AS market_number 
         FROM 
             ods_user 
         GROUP BY 
             market_id) t1
    JOIN
        dim_market t2
    ON 
        t1.market_id = t2.market_id;"
    """,
    """
    hive -e "
    INSERT INTO TABLE ads_opu (plays, comments, collections)
    SELECT 
        SUM(plays) AS plays,
        SUM(number_comments) AS comments,
        SUM(number_collections) AS collections
    FROM 
        ods_opu;"
    """
]

# 执行Hive命令
for command in hive_commands:
    print(f"Executing: {command}")
    result = subprocess.run(command, shell=True, check=True)
    print(f"Command executed with return code: {result.returncode}")